-- 用户主题插入数据
-- 开启本地模式
set hive.exec.mode.local.auto=true;
--1、用户启动日志表插入数据
insert overwrite table dwd_nshop.dwd_nshop_actlog_lauch
    partition (bdp_day = '${yesterday}')
select customer_id,
       device_num,
       device_type,
       os,
       os_version,
       manufacturer,
       carrier,
       network_type,
       area_code,
       case
           when from_unixtime(cast(ct / 1000 as int), 'HH') in (01, 02, 03, 04, 05) then 1
           when from_unixtime(cast(ct / 1000 as int), 'HH') in (06, 07, 08, 09, 10, 11, 12) then 2
           when from_unixtime(cast(ct / 1000 as int), 'HH') in (13, 14, 15, 16, 17, 18) then 3
           else 4 end,
       ct
from ods_nshop.ods_nshop_01_useractlog
where action = '02'
  and bdp_day = '${yesterday}';

-- 2、用户产品浏览表
insert overwrite table dwd_nshop.dwd_nshop_actlog_pdtview
    partition (bdp_day = '${yesterday}')
select customer_id,
       device_num,
       device_type,
       os,
       os_version,
       manufacturer,
       carrier,
       network_type,
       area_code,
       get_json_object(extinfo, '$.target_id'),
       duration,
       ct
from ods_nshop.ods_nshop_01_useractlog
where action in ('07', '08')
  and bdp_day = '${yesterday}';

-- 3、用户产品查询表
insert overwrite table dwd_nshop.dwd_nshop_actlog_pdtsearch partition (bdp_day = '${yesterday}')
select customer_id,
       device_num,
       device_type,
       os,
       os_version,
       manufacturer,
       carrier,
       network_type,
       area_code,
       get_json_object(extinfo, '$.target_order'),
       get_json_object(extinfo, '$.target_keys'),
       target_id,
       ct
from ods_nshop.ods_nshop_01_useractlog
         lateral view explode(split(regexp_replace(get_json_object(extinfo, '$.target_ids'), '[\\[\\]\"]', ''),
                                    ',')) t as target_id
where bdp_day = '${yesterday}'
  and action = '05'
  and event_type in ('01', '04');

-- 4、用户店铺关注表
insert overwrite table dwd_nshop.dwd_actlog_product_comment
    partition (bdp_day = '${yesterday}')
select customer_id,
       device_num,
       device_type,
       os,
       os_version,
       manufacturer,
       carrier,
       network_type,
       area_code,
       get_json_object(extinfo, '$.target_id'),
       ct
from ods_nshop.ods_nshop_01_useractlog
where action = '05'
  and event_type = '02'
  and get_json_object(extinfo, '$.target_action') = '01'
  and get_json_object(extinfo, '$.target_type') = '3'
  and bdp_day = '${yesterday}';



